# cython: language_level=3
#!/usr/bin/python
# -- coding:utf8 --
import os
import sys
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
# if __name__ == '__main__':
import model_vn.util_log as util_log
import model_vn.utils as utils
from model_vn.utils import select_sql_mysql
# else:
#     import app.model_vn.util_log as util_log
#     import app.model_vn.utils as utils
#     from app.model_vn.utils import select_sql_mysql
import time
import pandas as pd
import numpy as np
import json
import re
import warnings

file_path_name1 = "./model/sms_type_dict_vn_v1.json"
current_path = os.path.dirname(__file__)
file_path_name1 = file_path_name1.replace("./", current_path + "/")
with open(file_path_name1, "rb") as f:
    message_type_dict = json.loads(f.read())

def get_sms_data(phone_sub, ApplyNO):
    # sms_LoanFuzzyApp_cnt_1d_by_all_cnt_rat  近1天疑似贷款申请的短信条数占所有短信条数占比 
    # sms_LoanFuzzyApp_cnt_45d_by_all_cnt_rat  近45天疑似贷款申请的短信条数占所有短信条数占比 衍生逻辑
    # 获取sms数据的SQL逻辑
    sql_str = f"""
    select 
    ThisApplyNo as ApplyNO
    ,Name
    ,MemberPhone as My_phone
    ,Body as sms_content
    ,Date as Time
    ,CreateDate as SMS_CREATE_TIME
    ,TIMESTAMPDIFF(HOUR,Date,CreateDate) as TimeInterval
    ,SUBSTRING(Date,1,10) as MessageDays
    from fk_smslog_{phone_sub} 
    where ThisApplyNO = '{ApplyNO}'
    """
    # 获取SMS数据集
    sms_data = select_sql_mysql(sql_str)
    return sms_data

def get_free_sms_features(phone_sub, ApplyNO):
    app_df = get_sms_data(phone_sub, ApplyNO)
    return calculate_sms_features(app_df)

def calculate_sms_features(sms_df):
    try:
        if sms_df.empty:
            return {
                'sms_LoanFuzzyApp_cnt_1d_by_all_cnt_rat': None,
                'sms_LoanFuzzyApp_cnt_45d_by_all_cnt_rat': None,
            }
        
        ################## sms变量衍生逻辑 #################
        sms_df['sms_content']=sms_df['sms_content'].apply(lambda x:utils.decode_str(str(x)))
        # 匹配的字典 message_type_dict
        #根据短信内容去重
        sms_df.drop_duplicates(subset='sms_content',inplace=True)
        #计算短信条数
        df_sms_main=sms_df.groupby('ApplyNO')['sms_content'].nunique().reset_index().rename(columns={'sms_content':'sms_all_cnt'})
        df_sms_main
        #匹配短信类别
        for key,value in message_type_dict.items():
            dt1=[]
            for val in value:
                dt1.append(sms_df['sms_content'].apply(lambda x: x if str(val).lower().replace(' ','') in str(x).lower().replace(' ','') else np.nan))
            sms_df[f'sms_{key}_cnt'] = pd.DataFrame(dt1).bfill(axis=0).iloc[0]
        
        time_dict={'1d':24,'3d':72,'5d':120,'7d':168,'14d':336,'30d': 720,'45d':1080,'60d':1440,'90d':2160}
        df_sms_24=sms_df[sms_df['TimeInterval']<=24]
        #求种类为LoanFuzzyApp的近1天的短信条数
        df_sms_24_cnt=df_sms_24.groupby('ApplyNO')['sms_LoanFuzzyApp_cnt'].count().reset_index().rename(columns={'sms_LoanFuzzyApp_cnt':'sms_LoanFuzzyApp_cnt_1d'})
        df_sms_main=df_sms_main.merge(df_sms_24_cnt,how='left',on='ApplyNO')
        #求种类为LoanFuzzyApp的近45天的短信条数
        df_sms_1080=sms_df[sms_df['TimeInterval']<=1080]
        df_sms_1080_cnt=df_sms_1080.groupby('ApplyNO')['sms_LoanFuzzyApp_cnt'].count().reset_index().rename(columns={'sms_LoanFuzzyApp_cnt':'sms_LoanFuzzyApp_cnt_45d'})
        df_sms_main=df_sms_main.merge(df_sms_1080_cnt,how='left',on='ApplyNO')
        df_sms_main['sms_LoanFuzzyApp_cnt_1d_by_all_cnt_rat']=df_sms_main['sms_LoanFuzzyApp_cnt_1d']/df_sms_main['sms_all_cnt']
        df_sms_main['sms_LoanFuzzyApp_cnt_45d_by_all_cnt_rat']=df_sms_main['sms_LoanFuzzyApp_cnt_45d']/df_sms_main['sms_all_cnt']
        
        dict_main = df_sms_main.to_dict(orient='records')
        dict_main = dict_main[0]
        
        return {
            'sms_LoanFuzzyApp_cnt_1d_by_all_cnt_rat': dict_main['sms_LoanFuzzyApp_cnt_1d_by_all_cnt_rat'],
            'sms_LoanFuzzyApp_cnt_45d_by_all_cnt_rat': dict_main['sms_LoanFuzzyApp_cnt_45d_by_all_cnt_rat'],
        }

    except Exception as e:
        util_log.get_logger().error(e)
        return {
            'sms_LoanFuzzyApp_cnt_1d_by_all_cnt_rat': None,
            'sms_LoanFuzzyApp_cnt_45d_by_all_cnt_rat': None,
        }

if __name__ == '__main__':
    # 测试用例
    # phone_sub = 1
    # ApplyNO = '1679732000463928'
    # 测试用例
    phone_sub = 6
    ApplyNO = '168588033512616125'
    sms_features = get_free_sms_features(phone_sub, ApplyNO)
    print(sms_features)
